﻿using Microsoft.EntityFrameworkCore;
using sqlserverdemo.ado;
using sqlserverdemo.entitys;
using sqlserverdemo.orm;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;



namespace sqlserverdemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Server=localhost;Database=test;User Id=sa;Password=881213;Encrypt=True;TrustServerCertificate=True;";
            // 连接数据库
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    Console.WriteLine("数据库连接成功！");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("数据库连接失败: " + ex.Message);
                }
            }

            // ADO插入数据示例
            DbHelper helper = new DbHelper(connectionString);
            var users = new List<TUser>
            {
                new TUser { Name = "李四", Age = 30, Mail = "lisi@example.com", Sex = "男", Addr = "上海市", Phone = "13900000001" },
                new TUser { Name = "王五", Age = 28, Mail = "wangwu@example.com", Sex = "女", Addr = "广州市", Phone = "13900000002" }
            };

            int rows = helper.InsertUsers(users);
            Console.WriteLine($"批量插入成功，受影响行数: {rows}");
            Console.WriteLine("Hello, World!");


            // 使用ORM方式操作数据库
            using (var db = new MyDbContext())
            {
                // 添加
                //db.TUsers.Add(new TUser { Name = "张三", Age = 25, Sex = "男", Addr = "广州", Mail = "malfendou@163.com", Phone = "123456789" });
                //db.SaveChanges();

                //db.TAddrs.Add(new TAddr{Addr = "广州"});
                //db.SaveChanges();

                // 一对多关联添加
                //var user = new TUser
                //{
                //    Name = "叶静",
                //    Age = 30,
                //    Sex = "男",
                //    Mail = "mmmm",
                //    Phone = "123456789",
                //    Addr = "广州",
                //    Addrs = new List<TAddr>
                //    {
                //        new TAddr { Addr = "广州" },
                //        new TAddr { Addr = "佛山" },
                //        new TAddr { Addr = "武汉" },
                //        new TAddr { Addr = "潜江" },
                //    }
                //};

                //db.TUsers.Add(user);
                //db.SaveChanges();

                // 多对多关联添加


                // 查询用户
                //var user = db.TUsers
                //    // Include 角色导航属性
                //    .Include(u => u.Roles)
                //    .FirstOrDefault(u => u.Name == "叶静");
                //if (user == null)
                //{
                //    Console.WriteLine("未找到用户：叶静");
                //    return;
                //}

                //// 查询name=张六的用户
                //var userZhangLiu = db.TUsers
                //    // Include 角色导航属性
                //    .Include(u=>u.Roles)
                //    .FirstOrDefault(u => u.Name == "张六");
                //if (userZhangLiu == null)
                //{
                //    Console.WriteLine("未找到用户：张六");
                //    return;
                //}


                //// 检查角色是否已存在，若不存在则创建
                //var roleAdmin = db.TRoles.FirstOrDefault(r => r.Name == "管理员");
                //if (roleAdmin == null)
                //{
                //    roleAdmin = new TRole { Name = "管理员" };
                //    db.TRoles.Add(roleAdmin);
                //}

                //var roleTest = db.TRoles.FirstOrDefault(r => r.Name == "测试");
                //if (roleTest == null)
                //{
                //    roleTest = new TRole { Name = "测试" };
                //    db.TRoles.Add(roleTest);
                //}

                //// 关联角色
                //user.Roles ??= new List<TRole>();
                //if (!user.Roles.Contains(roleAdmin))
                //    user.Roles.Add(roleAdmin);
                //if (!user.Roles.Contains(roleTest))
                //    user.Roles.Add(roleTest);

                //userZhangLiu.Roles ??= new List<TRole>();
                //if (!userZhangLiu.Roles.Contains(roleAdmin))
                //    userZhangLiu.Roles.Add(roleAdmin);


                //db.SaveChanges();
                //Console.WriteLine("关联成功！");


                // 添加多条数据，并使用事务
                //using (var transaction = db.Database.BeginTransaction())
                //{ 
                //    try
                //    {
                //        db.TUsers.Add(new TUser
                //        {
                //            Name = "张四",
                //            Age = 25, 
                //            Addr = "广州", 
                //            Sex = "男",
                //            Phone = "123456789",
                //            Mail = "m"
                //        });
                //        db.TUsers.Add(new TUser
                //        {
                //            Name = "张五",
                //            Age = 23,
                //            Sex = "男",
                //            Phone = "123456789",
                //            Addr = "广州",
                //            Mail = "m"
                //        });
                //        db.TUsers.Add(new TUser
                //        {
                //            Name = "张六",
                //            Age = 45,
                //            Sex = "男",
                //            Phone = "123456789",
                //            Addr = "广州",
                //            Mail = "m"
                //        });

                //        db.SaveChanges();
                //        transaction.Commit();

                //    }
                //    catch (Exception ex)
                //    {
                //        Console.WriteLine($"发生异常: {ex.Message}");
                //        transaction.Rollback();
                //    }
                //}




                // 查询
                //var usersOrm = db.TUsers.Where(u => u.Age > 20).ToList();
                //foreach (var user1 in usersOrm)
                //{
                //    Console.WriteLine(user1);
                //}
                // 修改
                //var user = db.TUsers.FirstOrDefault();
                //if (user != null)
                //{
                //    user.Name = "李四";
                //    db.SaveChanges();
                //}

                // 删除
                //var delUser = db.TUsers.FirstOrDefault(u => u.Name == "李四");
                //if (delUser != null)
                //{
                //    db.TUsers.Remove(delUser);
                //    db.SaveChanges();
                //}


                // 查询用户及其角色信息
                var user = db.TUsers
                    // Include 角色导航属性
                    .Include(u => u.Roles)
                    .Include(u => u.Dept)
                    .FirstOrDefault(u => u.Name == "叶静");

                if (user == null)
                    {
                    Console.WriteLine("未找到用户：叶静");
                    return;
                }
                else
                {
                    // 输出roles信息
                    Console.WriteLine($"用户：{user.Name}，角色数量：{user.Roles.Count}");
                    var roles = user.Roles.Select(r => r.Name).ToList();
                    Console.WriteLine($"用户：{user.Name}，角色列表：{string.Join(", ", roles)}");
                }

                // 给用户叶静添加部门
                var dept = db.TDepts.FirstOrDefault(d => d.Name == "技术部");
                if (dept == null)
                {
                    dept = new TDept { Name = "技术部" ,Addr = "广州"};
                    db.TDepts.Add(dept);
                }
                else
                {
                    if(dept.Addr == null)
                    {
                        dept.Addr = "广州";
                    }
                }

                // 关联用户和部门
                if (user.Dept == null)
                {
                    user.Dept = dept;
                }
                db.SaveChanges();



            }
        }
    }
}
